if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MapStagedFacultyFromInfoEd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MapStagedFacultyFromInfoEd]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/***************************************************************************
* Name:			MapStagedFacultyFromInfoEd
* Purpose:		Maps the FacultyID from InfoEd to the AdmPersonId from Insight 
*				based first on NTLogin/username matching, then 
*				based on FacultyMap mapping
*
***************************************************************************/
CREATE PROCEDURE dbo.MapStagedFacultyFromInfoEd
AS
BEGIN

	SET NOCOUNT ON
	
	-- Username match
	Update F set AdmPersonId = Per.Id
		From Stg_IE_Faculty F
		inner join FwkDomainUser U
			on U.UserName = F.LoginId
		inner join AdmPerson Per
			on U.Id = Per.FwkDomainUserId

	-- FacultyId Match
	Update F set AdmPersonId = Map.AdmPersonId
		From Stg_IE_Faculty F
		inner join AdmPerson_InfoEdFaculty_Map Map
			On Map.Unique_Id = F.Unique_Id
		Where F.AdmPersonId is NULL

	-- Finally Map on RecomID for those additional peopler.
	Update F set AdmPersonId = Map.AdmPersonId
		From Stg_IE_Faculty F
		inner join AdmPerson_RecomAbstract_Map Map
			On Map.RecomAbstractId = F.RecomId
		WHERE F.AdmPersonId is NULL and F.RecomId is not null

	
	-- Mark all of the new MAP Records to be inserted.
	UPDATE Stg_IE_Faculty set IsNewMapRecord = 1 
		WHERE AdmPersonId IS NOT NULL AND Unique_ID IS NOT NULL AND Unique_ID not in (SELECT DISTINCT(Unique_ID) FROM AdmPerson_InfoEdFaculty_Map)

	-- Mark all the external people to be inserted.
	UPDATE Stg_IE_Faculty SET IsNewExternalPerson = 1 
		WHERE AdmPersonId is NULL AND Unique_ID IS NOT NULL AND (LoginID is Null OR LoginID like 'EXT_%')

	-- MARK Those invalid records
	UPDATE Stg_IE_Faculty SET IsInvalidRecord = 1 
		WHERE Unique_ID IS NULL

	SET NOCOUNT OFF

	END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO